package jk;

import user.Users;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class UserDao2 {
    //
    private Connection con=null;
    private Statement st=null;
    private ResultSet rs=null;
    public Connection getConnection() throws SQLException, ClassNotFoundException {
        // 1，加载驱动类，反射
        Class.forName("oracle.jdbc.OracleDriver");
        // 2，获取连接.
        con = DriverManager.getConnection(
                "jdbc:oracle:thin:@localhost:1521:orcl1",
                "system",
                "ljmspaces");
        return con;
    }
    public int insert(String username, String password) throws ClassNotFoundException, SQLException {

// 1，获取连接.
        con = this.getConnection();
// 2执行sqL语句
//2,1创建sqL语句
        String sql = "insert into J_USER " + "values(SEQ_J_USER.nextval,'" + username + "','" + password + "',1)";
        //2，2创建Statement对象，执行 sqL语句
        st = con.createStatement();
        int i = st.executeUpdate(sql);
//3.资源释放
        close();
        return i;
    }
    //查询
    public List<Users> result() throws ClassNotFoundException, SQLException {
    //0.创建集合
        List<Users> user=new ArrayList<>();
        //1，获取连接.
        con = this.getConnection();
// 2执行sqL语句
//2,1创建sqL语句
        String sql1 = "select * from J_USER";
        st = con.createStatement();
        rs = st.executeQuery(sql1);
        while(rs.next()){
            int id=rs.getInt("id");
            String username1=rs.getString("username");
            String password1=rs.getString("password");
            int state=rs.getInt("state");
            Users user1=new Users(id,username1,password1,state);
            user.add(user1);
        }
        close();
        return user;
    }
    //删除
    public int delete(String username)throws ClassNotFoundException, SQLException{

        //1，获取连接.
        con = this.getConnection();
// 2执行sqL语句
//2,1创建sqL语句
        String sql2= "delete from J_USER where USERNAME='"+username+"'";
        st = con.createStatement();
        int i = st.executeUpdate(sql2);
        close();
        return i;
    }
    public int update(String username,String password)throws ClassNotFoundException, SQLException{

        //1，获取连接.
        con = this.getConnection();
// 2执行sqL语句
//2,1创建sqL语句
        String sql3="update J_USER set PASSWORD='"+password+"'where USERNAME='"+username+"'";
        st = con.createStatement();
        int i = st.executeUpdate(sql3);
        close();
        return i;

    }
    public void close() throws SQLException {
        if(st!=null){
            st.close();
        }
        if(con!=null){
            con.close();
        }
        if(rs!=null){
            rs.close();
        }
    }
   /* public static void main(String[] args) throws SQLException, ClassNotFoundException {
        UserDao userdao = new UserDao();
        //userdao.insert("jkl","894546");
        //userdao.result("aaa","123456");
       //userdao.result("jkl", "894546");
        //userdao.delete("jkl","894546");
        userdao.update("aaa","111111");
    }*/
}

